package servidor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

public class administrador {
	public static final String DRIVER_CLASS_NAME = "org.sqlite.JDBC";
	public static final String CONNECTION_URL = "jdbc:sqlite:bd2/usuarios.s3db";
	//da de alta a un usuario
	public static void altaUsuario(String u,String c) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "INSERT INTO usuarios(Usuario, contrasenya) VALUES ('"+u+"','"+c+"')";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//da de baja a un usuario
	public static void bajaUsuario(String u) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "DELETE FROM usuarios WHERE Usuario='"+u+"'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//modifica los datos de un usuario(usu)
	public static void updateUsuario(String usu,String u,String c) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE usuarios SET Usuario ='"+u+"', contrasenya ='"+c+"' WHERE Usuario='"+usu+"'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//conecta un usuario
	public static void conectarUsuario(String u,int l) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		LinkedList <Usuario> lUsuarios = listaConectados();
		
		if(lUsuarios.size()<l){
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE usuarios SET Estado ='true' WHERE Usuario='"+u+"'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
		}
		else{
			System.out.println("No se ha podido conectar");
			System.out.println("Se ha alcanzado el limite de usuarios conectados");
		}
		
	}
	//desconecta un usuario
	public static void desconectarUsuario(String u) throws SQLException, ClassNotFoundException  {
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		String sqlStatementString = null;
		Statement statement = null;
		statement = connection.createStatement();
		sqlStatementString = "UPDATE usuarios SET Estado ='false' WHERE Usuario='"+u+"'";
		statement.executeUpdate(sqlStatementString);
		statement.close();
	}
	//devuelve la lista de los usuarios conectados
	public static LinkedList<Usuario> listaConectados() throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		LinkedList <Usuario> lUsuario = new LinkedList<Usuario>();
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		boolean v=true;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM usuarios WHERE Estado='true'";
		resultSet = statement.executeQuery(sqlStatementString);
		while(resultSet.next())
		{
		
			String Us = resultSet.getString("Usuario");
			String Con = resultSet.getString("contrasenya");
				
			
			Usuario s= new Usuario(Us,Con,v);
			lUsuario.addLast(s);
		}
		resultSet.close();
		statement.close();
		
		return lUsuario;
	}
	
	public static LinkedList<Usuario> listaDesconectados()  throws SQLException, ClassNotFoundException
	{	
		Class.forName(DRIVER_CLASS_NAME);
		Connection connection = DriverManager.getConnection(CONNECTION_URL);
		
		LinkedList <Usuario> lUsuario = new LinkedList<Usuario>();
		
		String sqlStatementString = null;
		Statement statement = null;
		ResultSet resultSet = null;
		boolean v = false;
		statement = connection.createStatement();
		sqlStatementString = "SELECT * FROM usuarios WHERE Estado='false'";
		resultSet = statement.executeQuery(sqlStatementString);
		while(resultSet.next())
		{
		
			String Us = resultSet.getString("Usuario");
			String Con = resultSet.getString("contrasenya");
				
			
			Usuario s= new Usuario(Us,Con,v);
			lUsuario.addLast(s);
		}
		resultSet.close();
		statement.close();
		
		return lUsuario;
	}
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException
	{
	int l =3;
	String a="iker";
	String b="xabier";
	String c="david";
	String d="aaa";
	
	conectarUsuario(a,l);
	conectarUsuario(b,l);
	conectarUsuario(c,l);
	conectarUsuario(d,l);
	
	
}
}
